﻿using System;
using MySql.Data.MySqlClient;
using System.Windows;
using System.Data;
using System.IO;
using System.Windows.Threading;

namespace TravellerApp1
{
    internal class MysqlHelper
    {
        private static MySqlConnection con = new MySqlConnection();
        private static string? strcon;

        /// <summary>
        /// 初始化，验证Id与Pwd
        /// </summary>
        /// <param name="server"></param>
        /// <param name="uid"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public static bool InitializeConnection(string server, string uid, string pwd)
        {
            strcon = "Data Source=" + server + ";Persist Security Info=yes;UserId=" + uid + ";PWD=" + pwd + ";";
            try
            {
                con.ConnectionString = strcon;
                con.Open();
                MessageBox.Show("连接成功");
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "错误", MessageBoxButton.OK, MessageBoxImage.Error);
                return false;
            }
        }

        public static bool DatabaseExist(string dbname)
        {
            string strsql = string.Format(@"SELECT * FROM information_schema.SCHEMATA where SCHEMA_NAME='" + dbname + @"'");
            con.Open();
            MySqlDataAdapter sda = new(strsql, strcon);
            con.Close();
            DataSet ds = new();
            sda.Fill(ds);
            return (ds.Tables[0].Rows.Count > 0);
        }

        public static void CreateDatabase(string dbname)
        {
            string strCreateDatabase = "CREATE DATABASE " + dbname + ";";
            con.Open();
            MySqlCommand cmdCreateDatabase = new(strCreateDatabase, con);
            cmdCreateDatabase.ExecuteNonQuery();
            con.Close();
        }

        /// <summary>
        /// 此后在travel947数据库上进行操作
        /// </summary>
        /// <param name="dbname"></param>
        public static void SetDatabase(string dbname)
        {
            if(con.State == ConnectionState.Closed)
                con.Open();

            con.ChangeDatabase(dbname);
            con.Close();
        }

        public static int ExecuteSqlScript(string str, int type)
        {
            con.Open();
            MySqlScript script = new MySqlScript(con);
            string sptText = str;
            if(type == 1)
            {
                FileInfo file = new FileInfo(str);
                sptText = file.OpenText().ReadToEnd();
            }
            script.Query = sptText;

            SetDatabase("travel947");
            int cnt = script.Execute();
            con.Close();
            return cnt;
        }

        public static MySqlDataReader ExecuteSqlScript(string str)
        {
            con.Open();
            MySqlCommand script = con.CreateCommand();
            script.CommandText = str;
            MySqlDataReader read = script.ExecuteReader();
            return read;
        }

        public static string GetConnectionString()
        {
            return con.ConnectionString;
        }

        public static void CloseCon()
        {
            con.Close();
            con.Dispose();
        }

    }
}
